Systems and methods for mutations and operational transforms in a collaborative spreadsheet environment

ABSTRACT

Mutations representing spreadsheet edit operations are received at a server from client computers of collaborators and also at a collaborator&#39;s client computer from other collaborators and the server. Different mutations may conflict, i.e., provide contradictory instructions on how a spreadsheet is to be edited. Techniques for representing sort operations, cut-and-paste operations, and operations to change cell properties as mutations, and operational transform techniques that can be used to resolve conflicts between such mutations, are disclosed herein. Further disclosed herein are techniques for identifying and processing computationally intensive types of mutations in a calculation thread which operates asynchronously with respect to a UI thread at a collaborator&#39;s client computer. The processing may include performing an operational transform on results of the calculation thread based on results obtained in the UI thread.

FIELD

This disclosure relates generally to techniques for resolving conflictsthat arise among edit commands entered by different users in acollaborative spreadsheet environment.

BACKGROUND

Conflicting edits of a spreadsheet may be received when the spreadsheetis accessed by multiple collaborators. For example, a first user mayselect to perform an edit of a first region of cells of the spreadsheetand a second user may perform an edit of a second region of cells of thespreadsheet overlapping the first region. In order for all collaboratorsto have a common (i.e., consistent) spreadsheet, such conflicts shouldbe resolved.

SUMMARY

In a collaborative network environment, mutations representingspreadsheet edit operations are received at a server from computers ofvarious collaborators. Mutations are also received at eachcollaborator's client computer from other collaborators and the server.In each location, mutations are queued in a sequence to be applied tothe spreadsheet and a first mutation and a second mutation present inthe queue may conflict. As used in the disclosure herein, a firstmutation and a second mutation are said to conflict if they providecontradictory instructions on how the spreadsheet is to be edited orelse require that the second mutation be interpreted and/or modified inview of the first mutation before the second mutation is applied to thespreadsheet to produce a desired command or result. Such conflicts canarise with respect to sort operations, cut-and-paste operations,operations to change cell properties, and other types of operations.Accordingly, this disclosure relates to techniques for representingspreadsheet operations as mutations and to operational transformtechniques for resolving conflicts among such mutations.

In the collaborative network environment, a collaborator may entermultiple edits of a spreadsheet, each of which is represented by arespective mutation and applied locally to the spreadsheet at thecollaborator's client computer. Mutations involving analytic functionsare identified as computationally intensive and hence processed in acalculation thread which operates asynchronously with respect to a UIthread at the collaborator's client computer. Further, operationaltransforms are used to modify results of the calculation thread based onresults obtained in the UI thread. As used herein, the term UI threadrefers to a “main” thread of a process. That is, the UI thread is aninitial (i.e., first) thread used to execute a given process and the UIthread spawns any other (secondary) threads employed by the process.Further, in some implementations, the UI thread handles system calls inconnection with other threads in use by a process. The terms UI threadand main thread are used interchangeably in this disclosure.

Accordingly, disclosed herein are techniques for updating a spreadsheetbased on user inputs. Specifically, a first mutation from a first sourcelocation specifying a function to be applied to data of the spreadsheetis received, where the first mutation comprises one or moresource-row-to-destination-row mappings. Further, a second mutation froma second source location specifying a data edit mutation to be appliedto the spreadsheet is received, where the second mutation has a higherpriority than the first mutation. The second mutation is applied to thespreadsheet, and an operational transform is performed on the firstmutation based on the second mutation to generate a modified firstmutation. The modified first mutation is applied to the spreadsheet.

Also disclosed herein are techniques for updating a property of a cellin a collaborative spreadsheet. Specifically, a first mutation isreceived from a first source, where the first mutation comprises a valueof a first cell property of a cell in the spreadsheet. Further, a secondmutation is received from a second source, where the second mutationcomprises a value of a second cell property of the cell in thespreadsheet. It is determined that the first cell property and thesecond cell property belong to a common property field, and anoperational transform is performed based on the first mutation and thesecond mutation to generate a modified mutation. A property of the cellin the spreadsheet is updated based on the modified mutation.

Further disclosed herein are techniques for updating a spreadsheet basedon user inputs. Specifically, a first user input is received specifyingan analytic function to be applied to data of the spreadsheet and asecond user input is received specifying a data edit operation to beperformed on the spreadsheet. Further, the first user input is processedin a calculation thread, where the processing comprises applying theanalytic function to the data of the spreadsheet to produce firstmutation results. While the first user input is processed in thecalculation thread, the second user input is processed in a UI thread toproduce second mutation results, and the second mutation results areapplied to the spreadsheet to update the spreadsheet. In response to adetermination that the processing of the first user input is complete,an operational transform is performed on the first mutation resultsbased on the second mutation results to obtain modified first mutationresults, and the modified first mutation results are applied to thespreadsheet.

BRIEF DESCRIPTION OF THE DRAWINGS

Further features of the disclosed techniques, their nature and variousadvantages, will be apparent upon consideration of the followingdetailed description, taken in conjunction with the accompanyingdrawings, in which like reference characters refer to like partsthroughout, and in which:

FIG. 1 depicts a client-server system, where the server supports a cloudcomputing system for storing spreadsheets and other files in accordancewith an implementation;

FIG. 2 depicts an exemplary client computer in accordance with animplementation;

FIG. 3 depicts an illustrative portion of an spreadsheet that may bestored on a cloud computing system in accordance with an implementation;

FIG. 4 depicts an illustrative global mutation log in accordance with animplementation;

FIG. 5 illustrates a process for asynchronously processing mutations andapplying operational transforms to results of the processing inaccordance with an implementation;

FIG. 6 illustrates a single column of a spreadsheet in accordance withan implementation;

FIG. 7 illustrates a sort (reordering) mutation corresponding to thesingle column of FIG. 6 in accordance with the implementation depictedin FIG. 6;

FIG. 8 illustrates a workflow including a sort mutation and anoperational transform of a sort mutation in accordance with animplementation;

FIG. 9 illustrates an operational transform of a cut-and-paste mutationbased on a higher priority set-cell mutation in accordance with animplementation;

FIG. 10 illustrates a workflow in which a cut-and-paste mutation isprocessed at a server in a collaborative spreadsheet environment inaccordance with an implementation; and

FIG. 11 illustrates an operational transform process by which cellproperty mutations entered by two collaborators to a common spreadsheetmay be resolved in accordance with an implementation.

DETAILED DESCRIPTION

The systems and methods described herein relate to a cloud computingsystem capable of delivering a spreadsheet to multiple remote clientusers and for implementing edits (also referred to herein as“mutations”) to the spreadsheet made by the client users. In someimplementations, the systems and methods disclosed herein areimplemented by a cloud computing system similar or identical to thatdescribed in copending, commonly-assigned U.S. patent application Ser.No. 13/735,377, which was filed on Jan. 7, 2013, entitled “DYNAMICALLYSIZING CHUNKS IN A PARTIALLY LOADED SPREADSHEET MODEL” (Attorney DocketNo. 108827-1126-101), the disclosure of which is hereby incorporated byreference herein in its entirety.

Some aspects of a representative cloud computing system on which thedisclosed systems and methods may be implemented are described next. Insuch a cloud computing system, a global mutation log is associated witha spreadsheet and stored on a server. The global mutation log recordsmutations made by users to the spreadsheet. When a user on a clientcomputer requests the spreadsheet from the server, the server appliesthe mutations stored in the global mutation log to the spreadsheet andsends the spreadsheet, or a portion of the spreadsheet (referred to as a“chunk” of the spreadsheet), to the client computer.

The user on the client computer may make local edits of the spreadsheet.These edits are also represented by mutations that are stored in apending queue on the client computer and that are sent in batches fromthe client computer to the server. The pending queue includes a sentsection for mutations that have been sent to the cloud computing systemand an unsent section for mutations that have not been sent to the cloudcomputing system. Mutations made by collaborators of the user of theclient computer are sent to the client computer and placed in acollaborator queue.

The server or a local client computer may include edits that “conflict”in its queue of edits that are to be applied to its copy of thespreadsheet. For example, the server may receive a first edit of a firstpart of the spreadsheet from a collaborator A and a second edit of asecond part of the spreadsheet from a collaborator B, where the firstand second parts of the spreadsheet overlap and specify differentoperations. Continuing the example, edit A may specify numerical valuesfor data cells in the first part of the spreadsheet while edit B mayspecify numerical values for data cells in the second part of thespreadsheet. A conflict occurs for a cell included in both the first andsecond parts of the spreadsheet when the numerical value for the cellspecified by the edit A is different than the numerical value for thecell specified by the edit B.

Operational transforms are used to resolve such conflicts and ensureconsistency of results regardless of the order in which the mutationsare applied. In some instances, an unsent pending user mutation in thepending queue is operationally transformed by the client computeragainst the collaborator mutations before being sent to the cloudcomputing system. In other instances, the client computer sends apending user mutation to the cloud computing system without performingan operational transform on the pending user mutation, and the cloudcomputing system itself performs the operational transform on thepending user mutation when applying the pending user mutation to thespreadsheet. In either of these cases, the client computer may send thecloud computing system a revision number associated with the mutation.The cloud computing system then correctly transforms the mutationagainst any other collaborator mutations that have been applied to thecloud computing system's copy of the spreadsheet having a later revisionnumber before applying the (operationally transformed) mutation to thespreadsheet.

These and other features may be implemented using a system as shown inFIG. 1. In particular, FIG. 1 depicts a client-server system, where theserver supports a cloud computing system for storing spreadsheets andother files in accordance with an implementation. System 100 includesone or more servers 102 which collectively provide a cloud computingsystem for storing files such as spreadsheet file 104. System 100 alsoincludes a number of client computers 106 a through 106 d which connectto servers 102 through a remote network, such as the Internet. Each oneof client computers 106 a through 106 d may be a desktop computer,laptop computer, mobile device, tablet, or any other computing devicecapable of connecting with servers 102. The remote network connectionmay be a wired or wireless Internet connection, local area network(LAN), wide area network (WAN), Wi-Fi network, Ethernet, or any othertype of known connection.

FIG. 2 depicts an exemplary client computer in accordance with animplementation. The client computer 200 includes a central processingunit (CPU) 202, read only memory (ROM) 204, random access memory (RAM)206, input/output interface 208, data store 210, and bus 212. Clientcomputer 200 may have additional components that are not illustrated inFIG. 2. Bus 212 allows the various components of client computer 200 tocommunicate with each other. Input/output interface 208 allows clientcomputer 200 to communicate with other devices, such as one or moreservers hosting the cloud computing system. Data store 210 may store,among other things, code for a web browser for interacting with a cloudcomputing system and displaying and editing files stored on the cloudcomputing system. Data store 210 also stores one or more portions of aspreadsheet loaded from the cloud computing system.

Data store 210 for storing files and programs on client computer 200 maybe implemented using non-transitory computer-readable media. Examples ofsuitable non-transitory computer-readable media include all forms ofnon-volatile memory, media and memory devices, including by way ofexample semiconductor memory devices, e.g., EPROM, EEPROM, and flashmemory devices; magnetic disks, e.g., internal hard disks or removabledisks; magneto-optical disks; and readable, once-writeable, orre-writeable CD-ROM and DVD-ROM disks.

FIG. 3 depicts an illustrative portion of an spreadsheet that may bestored on a cloud computing system in accordance with an implementation.Spreadsheet 300 includes rows 1 through 14 and columns A through E. Thecloud computing system may represent the entire spreadsheet 300 usingone or more chunks, where each chunk represents a range of cells in thespreadsheet. A spreadsheet file may include a number of individualsheets, each having its own tab, arranged in a “workbook” structure.Chunks may be created for each sheet within the spreadsheet file.

A global mutation log is associated with the spreadsheet. FIG. 4 depictsan illustrative global mutation log 400 in accordance with animplementation. The global mutation log applies to all chunks of aspreadsheet, so only one log is stored per spreadsheet. Global mutationlog 400 stores mutations, or edits, that all users with write access tothe spreadsheet send to the cloud computing system, in the order inwhich they are received. These edits may be to set the value of cells,delete cell values, enter formulae into cells, cut, copy or pastevalues, add or delete rows and columns, or any other operationpermissible in an electronic spreadsheet. For example, global mutationlog 400 stores a number of set value commands, such as “Set A2=2” formutation A, “Set A3=4” for mutation B, and “Set B3=A2+A3” for mutationC. Global mutation log 400 may also store row addition and deletionmutations, such as “Delete Row 6” for mutation I and “Add Row 11” formutation J. Other mutations not shown in FIG. 4 may also be stored inglobal mutation log 400.

Asynchronous Spreadsheet Formula Calculation with Operational Transform

When a user of a client computer edits a spreadsheet, the edits arerepresented by mutations that are applied locally to the spreadsheet atthe client computer (in addition to being sent, in batches, from theclient computer to the server). If mutations are processed serially in amain (or UI) thread, then mutations that are computationally intensive(e.g., a mutation involving a mathematical formula that applies to largeregions of the spreadsheet) will consume a large amount of computationalresources at the client computer and delay application of later-queuedmutations to the spreadsheet. The serial processing of mutations in thismanner would result in a sluggish or non-responsive user experience.

As an alternative to the serial processing of mutations as describedabove, computationally intensive types of mutations may be identifiedand processed in a calculation thread which operates asynchronously withrespect to a UI thread, and operational transforms may be used to modifyresults of the calculation thread based on results obtained in the UIthread. In an implementation, mutations involving analytic functions areidentified as computationally intensive and thus processed in thecalculation thread asynchronously with respect to a UI thread.

As a preliminary illustrative example, suppose that a local useraccesses a sheet of a spreadsheet in which a value of 10 is present incell A1 and that the user enters the formula “=A1+100” in cell A2.Because the formula “=A1+100” is an analytic function, calculation ofthe formula is processed in a calculation thread that operatesseparately and asynchronously from the UI thread used to process allother user mutations (i.e., those not involving analytic functions).Further, while the calculation is processed by the calculation thread(assume, for the purposes of the example, that the formula takes a longtime to compute), a user may perform additional data edit operations tothe spreadsheet that are processed by the UI thread.

A suitable operational transform is used to modify the result of thecalculation thread based on a result of the UI thread so as to ensureconsistent results, if necessary. For example, suppose that the userinserts a row at row index one that is processed by the main threadwhile the calculation thread processes the mutation corresponding toentry of the formula “=A1+100.” In this case, the result of thecalculation thread that was to be assigned to cell A2 should instead beassigned to cell A3 instead (accounting for the row insertion). Thus,when the calculation of the calculation thread finishes and produces amutation corresponding to the analytic function, the mutation isoperationally transformed based on the insert row mutation output by theUI thread, so that the result of the analytic function is placed in thecorrect location of the spreadsheet (i.e., in cell A3 instead of in cellA2, in the current example). This process is formalized in process 500,below.

FIG. 5 illustrates a process for asynchronously processing mutations andapplying operational transforms to results of the processing inaccordance with an implementation. At 510, a first local client requestis received at the client computer (e.g., at any one of client computers106 a through 106 d of FIG. 1) to edit a spreadsheet based on ananalytic function. For example, the command “set B3=A2+A3” or “set A3=5”may be received at 510.

At 520, the client computer initiates a calculation thread in responseto receipt of the first local client request and begins to processspreadsheet data based on the analytic function using the calculationthread. The calculation thread is separate from a UI (or main) threadused by the client computer to process spreadsheet data and thecalculation thread operates asynchronously with respect to the UIthread.

At 530, a second local client request is received, where the secondlocal client request is to edit a spreadsheet based on a data editoperation. A data edit operation encompasses any edit operationspossible on the spreadsheet, including operations to insert or deleterows or columns, change a font size of data displayed in a cell orcells, and change a background or text color associated with a cell orcells.

At 540, the client computer begins processing spreadsheet data based onthe second local client request using the UI thread. At 550, the clientcomputer receives the mutation result from the calculation thread and,at 560, the client computer receives the mutation result from the UIthread. As would be understood by one of ordinary skill, based on thedisclosure and teachings herein, the mutation results from thecalculation thread may be received before, after, or at the same time asresults from the UI thread, and in general the time at which results arereceived from each thread depends on the computational complexity of thedata being processed by that thread.

At 565, results from the UI thread are applied to the copy of thespreadsheet on the local client computer and a corresponding mutation isstored in a pending queue on the client computer. At 580, it isdetermined if the mutation result from the UI thread conflicts with themutation result from the calculation thread (e.g., if the two resultsdictate inconsistent modifications to common spreadsheet cells). Ifthere is no conflict in the mutations, then process 500 proceeds to 585,where the results from the calculation thread are applied to the copy ofthe spreadsheet on the local client computer and a correspondingmutation is stored in a pending queue on the client computer.

On the other hand, if there is a conflict in the mutation results, thenprocess 500 proceeds to 570, where an operational transform is performedon the mutation results from the calculation thread in view of themutation results of the UI thread to produce modified mutation resultsfor the calculation thread. As would be understood by one of ordinaryskill, based on the disclosure and teachings herein, the nature of theoperational transform employed at 580 depends on a type associated witheach of the first client request and the second client request.Operational transforms for particular types of operations (e.g., sortoperations, cut-and-paste operations, and operations to change cellproperties) are described elsewhere in this disclosure. At 575, themodified mutation results from the calculation thread are applied to thespreadsheet.

Mutations and Operational Transforms for Sorting Data in a CollaborativeSpreadsheet

Conflicting mutations arise in relation to sorting spreadsheet data in acollaborative spreadsheet environment. As explained next, theseconflicts may be resolved by first specifying a structure to representsort mutations and then specifying suitable techniques for performing anoperational transform on the sort mutations. In designing mutations andoperational transforms for data sort commands, a couple of points are tobe noted. First, conflicting mutations produced by different users arereceived at local client computers (in addition to at a server). Second,because client computers typically maintain in memory at any one timeonly chunks of a spreadsheet, rather than an entire spreadsheet, thedesign described herein allows a client computer to apply a sortingmutation received from another user or the server without having toaccess any additional chunks of the spreadsheet other than those thatare already available to the client computer.

In particular, sort mutations are represented as a reordering of rowsand also include cell values of the data that is to be sorted. FIGS. 6and 7 together illustrate aspects of a sort mutation (which is alsoreferred to as a “reorder mutation” herein) in accordance with animplementation. In particular, FIG. 6 illustrates a single column (“A”)of a spreadsheet 600 in accordance with an implementation. Further, FIG.7 illustrates a sort (reordering) mutation corresponding to the singlecolumn of FIG. 6 in accordance with the implementation depicted in FIG.6. Suppose that a local client request is received to sort the columnlabeled “A” in ascending order. The sort mutation 750 represents thissort command. In particular, the sort mutation 750 includes threecolumns—column 743 stores the cell content of each origin row (i.e.,rows 1-6) of the spreadsheet 600, while columns 746 and 749 store amapping between origin and destination rows implied by the sort command.It should be noted that FIGS. 6 and 7 together illustrate a simplifiedcase in which only a single column of data is sorted. In general, a sortcommand applies to data spanning N columns. Thus, in the general case,column 743 would store, for each row, the contents of all columnscorresponding to that row included in the sort.

As described above, in an implementation, each collaborator on aspreadsheet may have a unique set of chunk(s) of the spreadsheet storedon his or her respective client computer. The structure of sort mutation750 nevertheless enables each client computer to apply a sortingmutation received from another collaborator or the server without havingto access any chunks of the spreadsheet other than those that arealready available locally on the client computer. Specifically, bystoring the actual cell contents subject to sorting (e.g., as stored incolumn 743 of illustrative reordering mutation 750), each clientcomputer is able to perform sorting operations based on a received rowreordering mutation without a need to read from or write to any otherchunk of the spreadsheet.

This point is illustrated by FIGS. 6 and 7. Referring back to thesefigures, assume that the spreadsheet 600 includes two chunks, chunk 610and chunk 620. Suppose further that collaborator A maintains a copy ofchunk 610 (containing rows 1-3) and not chunk 620, and that collaboratorB maintains a copy of chunk 620 (containing rows 4-6) and not chunk 610.Because the reordering mutation 750 stores cell contents subject tosorting, the reordering mutation 750 allows a collaborator to performsorting even without access to chunk(s) of the spreadsheet within whichorigin rows are located. For example, based on the reordering mutation750, collaborator A is able to update its local spreadsheet at rows 1and 2 with data that, prior to the sort, was located in origin rows 6and 5, respectively, i.e., data values that were not originally a partof A's local spreadsheet chunk 610. Similarly, collaborator B is able toupdate its local spreadsheet at rows 4 and 6 with data that, prior tothe sort, was located in origin rows 3 and 2, respectively, i.e., datavalues that were not originally in B's local spreadsheet chunk 620.

FIG. 8 illustrates a workflow including a sort mutation and anoperational transform of a sort mutation in accordance with animplementation. At 810 of process 800, the client computers of twocollaborators, A and B, receive chunk(s) of a spreadsheet from a server.The client computer of collaborator A and collaborator B will bereferred to as client computer A and client computer B, respectively.The chunk(s) received by the client computers A and B may be the same ormay be different. At 820, client computers A and B each go offline withrespect to the server and each other. At 830, collaborator A issues acommand to sort a rectangular range of cells denoted by MX:NY, i.e., therange of cells from row M to row N and from column X to column Y,inclusive.

At 840, client computer A creates a sort mutation based on thelocally-received command. For example, if the received sort mutationapplies to column A of the spreadsheet 600 depicted in FIG. 6, thenclient computer A creates the sort mutation 750 depicted in FIG. 7.Further, at 840, the client computer A applies the sort mutation to itslocal copy of the spreadsheet and places the sort mutation in itspending queue so that the row-reordering mutation may be sent to theserver and client computer B at a later time when client computer Acomes online.

At 850, collaborator B issues a command to change a value of data incell A1 of the spreadsheet. At 860, client computers A and B each comeonline. At 870, client computer A, upon coming online, sends the sortmutation to the server and to client computer B. Similarly, clientcomputer B, upon coming online, sends the change value mutation to theserver and to client computer A.

At 880, client computer A performs an operational transform on thechange value mutation received from client computer B based on its ownsort mutation to produce a modified change value mutation. That is, thechange value mutation received from client computer B is applied to cellA1 on collaborator B's copy of the spreadsheet, the operationaltransform performed by client computer A maps this to the equivalent rowon collaborator A's copy of the spreadsheet in view of the sortmutation. At 890, client computer A applies the modified change valuemutation to its local spreadsheet. Further, client computer B appliesthe sort mutation received from collaborator A directly to itsspreadsheet without any need for an operational transform.

That is, at 890, client computer B does not perform an operationaltransform of the sort mutation in view of the change value mutation butrather applies the sort mutation directly to its local copy of thespreadsheet. This is because the particular row reorderings that definea sort operation are not affected by a change to the underlying contentof data in any cell of any row. As such, the fact that client computer Adid not have knowledge of the change value mutation (issued bycollaborator B) at the time that client computer A issued the sortmutation does not alter the sort command that is to be applied to thelocal spreadsheet at client computer B.

Mutations and Operational Transformations for Cut-and-Paste

Conflicting mutations arise in relation to cut-and-paste operations in acollaborative spreadsheet environment. These conflicts may also beresolved using operational transforms. In particular, specified hereinare both a cut-and-paste mutation structure and a technique for applyingoperational transforms to cut-and-paste mutations. In someimplementations, cut-and-paste mutations are represented as a mappingfrom source cells to destination cells in a spreadsheet. For example,with reference to spreadsheet 300 of FIG. 3, suppose that a collaboratorissues a command to cut cells A1:A5 and paste these cells at locationB6:B10 of the spreadsheet. The corresponding mutation is a datastructure containing the mapping

Rows: 1→6, 2→7, 3→8, 4→9, 5→10, and

Columns: 1→2.

Represented in a table format for clarity of presentation, thecut-and-paste mutation is represented as

Rows Columns 1 → 6 1 → 2 2 → 7 1 → 2 3 → 8 1 → 2 4 → 9 1 → 2  5 → 10 1 →2This cut-and-paste mutation may then be operationally transformed basedon other spreadsheet commands, such as commands to insert row(s), deleterow(s), insert columns(s), delete column(s), and set cell contentvalues.

In specifying a cut-and-paste operation, a user specifies, e.g.,highlights with a mouse cursor, a region of one or more destinationcells in a spreadsheet where source cells are to be pasted. In doing so,it is possible that a user may designate a number of destination cellsthat is different from a number of source cells (referred to as a“mismatched” designation of cells). A user may make a mismatcheddesignation of cells either accidentally or, especially if the user isfamiliar with how the spreadsheet handles such mismatches, on purpose.In some implementations, a mismatched designation of cells is handled asfollows.

In some implementations, if a number of destination cells selected isfewer than a number of source cells selected, then the number ofdestination cells is automatically expanded to be of the same size asthe number of source cells. For example, if a user selects five cells,A1:A5, for cutting in a cut-and-paste operation but highlights only twocells, C4 and C5, for pasting, the paste operation would neverthelesspaste the contents of the cells A1:A5 into cells C4:C8. A cut-and-pastemutation corresponding to these user actions would reflect thisautomatic expansion of destination cells. Thus, the cut-and-pastemutation would be represented as

Rows Columns 1 → 4 1 → 3 2 → 5 1 → 3 3 → 6 1 → 3 4 → 7 1 → 3 5 → 8 1 → 3despite the fact that the user elected to paste only into two cells,cells C4 and C5.

On the other hand, if a number of destination cells selected is largerthan a number of source cells selected in a cut-and-paste operation,then, in some implementations, source cells are “tiled” to fit theentire region of selected destination. In some implementations, tilingis only performed (i) if a number of destination rows and destinationcolumns are each a common integer multiple of a number source rows andcolumns, respectively, (ii) there is one source row and one destinationrow and a number of destination columns is an integer multiple of anumber of source columns, or (iii) there is one source column and onedestination column and a number of destination rows is an integermultiple of a number of source rows. For example, if a user designatesthree cells A1:A3 for a cut and designates twelve cells B1:C6 for paste,the number of destination rows and destination columns are each a commoninteger multiple (i.e., the integer multiple two) of the number sourcerows and columns. Accordingly, the source cells would be “tiled” fourtimes, with an instance of the contents of cells A1:A3 pasted in each ofcell regions B1:B3, B4:B6, C1:C3, and C4:C6. Thus, the cut-and-pastemutation would be represented as

Rows Columns 1 → 1 1 → 2 2 → 2 1 → 2 3 → 3 1 → 2 1 → 4 1 → 2 2 → 5 1 → 23 → 6 1 → 2 1 → 1 1 → 3 2 → 2 1 → 3 3 → 3 1 → 3 1 → 4 1 → 3 2 → 5 1 → 33 → 6 1 → 3

In some implementations, the spreadsheet program includes functionalityto ensure that the behavior described above in response to a mismatcheddesignation of cells conforms to a user's intention (e.g., to preventagainst the possibility that the user accidentally designated a mismatchof cells and/or to ensure that the user knows how mismatches are handledby the spreadsheet program). For example, in some implementations, theuser is notified of the mismatch and prompted to confirm theconsequential behavior of the mismatch (i.e., the tiling or expandingcells, as described above).

One characteristic of the mutation representation of cut-and-pasteoperations described above is that it is straightforward to break large(and therefore, potentially computationally complex) mutations into anumber of smaller mutations. For example, the mutation above may bedecomposed into four separate and smaller mutations as follows

Rows Columns 1 → 1 1 → 2 2 → 2 1 → 2 3 → 3 1 → 2

Rows Columns 1 → 4 1 → 2 2 → 5 1 → 2 3 → 6 1 → 2

Rows Columns 1 → 1 1 → 3 2 → 2 1 → 3 3 → 3 1 → 3

Rows Columns 1 → 4 1 → 3 2 → 5 1 → 3 3 → 6 1 → 3Clearly, a large mutation may be broken into smaller mutations accordingto a variety of different formats. For example, instead of foursubmutations of three rows per mutation, as in the example above, analternate scheme would include six submutations of two rows permutation. Further, there is no requirement that all submutations includethe same number of rows per mutation.

As described above in relation to sort mutations, client computerstypically maintain in memory at any one time only chunks of aspreadsheet, rather than an entire spreadsheet. Accordingly, in someimplementations, the design described herein allows a client computer toapply a cut-and-paste mutation received from another user or the serverwithout having to access any additional chunks of the spreadsheet otherthan those that are already available to the client computer.Specifically, in these implementations, a cut-and-paste mutationincludes data values that are to be pasted in addition to the mappingfrom source cells to destination cells in a spreadsheet described above.For example, with reference to spreadsheet 300 of FIG. 3, suppose that acollaborator issues a command to cut cells A1:A5 and paste these cellsat location B6:B10 of the spreadsheet. The corresponding mutation is adata structure containing the mapping

Rows: 1→6, 2→7, 3→8, 4→9, 5→10,

Values: v₁, v₂, v₃, v₄, v₅, and

Columns: 1→2,

where v₁ through v₅ are the values of the data stored at spreadsheetcells A1 through A5, respectively, just prior to the cut-and-pasteoperation. Accordingly, each client computer is able to perform a pastefunction of a cut-and-paste operation based on a received cut-and-pastemutation without a need to read from or write to any other chunk of thespreadsheet.

Operational transforms of cut-and-paste mutations based on the insertionand deletion of rows are handled as follows. When transforming acut-and-paste mutation against a higher priority insert row mutationthat intersects source cells only, a copy of the inserted row is notincluded in the destination cells. As an example, suppose that a usercommand is received to insert a row 2. The cut-and-paste mutation

Rows Columns 1 → 4 1 → 3 2 → 5 1 → 3 3 → 6 1 → 3would be transformed to

Rows Columns 1 → 5 1 → 3 3 → 6 1 → 3 4 → 7 1 → 3Thus, the first, third, and fourth rows are mapped to destination rows,but the (newly inserted) second row is not.

When transforming a cut-and-paste mutation against a higher prioritydelete row mutation that intersects source cells only, a copy of thedeleted row is not included in the destination cells. For example,suppose a command is received to delete a row 2. The cut-and-pastemutation Ml, above, would be transformed to

Rows Columns 1 → 4 1 → 3 3 → 5 1 → 3Thus, the (deleted) second source row is not copied to the destination,although the first and third source rows are.

When transforming a cut-and-paste mutation against a higher priorityinsert row mutation that intersects destination cells only, the paste isperformed “around” the inserted row. For example, suppose a command isreceived to insert a row 5. The cut-and-paste mutation Ml, above, wouldbe transformed to

Rows Columns 1 → 4 1 → 3 2 → 6 1 → 3 3 → 7 1 → 3Thus, data is pasted into the third column of the fourth, sixth, andseventh rows, but not into the third column of the fifth row (i.e., thenewly inserted row).

When transforming a cut-and-paste mutation against a higher prioritydelete row mutation that intersects destination cells only, the pasteoperation omits pasting source data that was intended for the deletedrow. For example, suppose a command is received to delete a row 5. Thecut-and-paste mutation Ml, above, would be transformed to

Rows Columns 1 → 4 1 → 3 2 → EMPTY 1 → 3 3 → 6 1 → 3Thus, as indicated by the qualifier “EMPTY,” the source data in column 1of row 2 is not pasted anywhere as this data was intended to be pastedinto the (since deleted) row 5. Although not pasted, in someimplementations, the contents of the source at column 1 and row 2 arenevertheless stored in a memory in case the contents are needed for alater operational transform or mutation.

When transforming a cut-and-paste mutation against a set-cell command(i.e., a command setting or changing a formula assigned to a cell in aspreadsheet), the set-cell command is updated only when the set-cellcommand solely references cells located within the region of cells thatare cut. FIG. 9 illustrates such an operational transform of acut-and-paste mutation based on a higher priority set-cell mutation forthe case that cells A1 and B1 of sheet 1 are cut-and-paste to cells A2and B2, respectively, of sheet 2. Specifically, sheet 1 900-1 and sheet2 925-1 depict formulas entered into cells of (portions of) a sheet 1and a sheet 2, respectively, of a given spreadsheet prior to acut-and-paste of cells A1 and B1 of sheet 1 into cells A2 and B2,respectively, of sheet (in FIG. 9, a blank cell indicates that noformula has been entered into that cell). Similarly, sheet 1 900-2 andsheet 2 925-2 depict formulas effective for cells of (portions of) thesheet 1 and the sheet 2, respectively, of the spreadsheet after thecut-and-paste operation.

The changes in the cell formulas before and after the cut-and-pasteoperation illustrate the rule that a set-cell command is updated onlywhen the set-cell command exclusively references cells located withinthe region of cells that is cut. First, because the formula for cell A1of sheet 1 is a constant value, it does not reference any cells at all,and thus is not changed when moved to cell A2 of sheet 2. On the otherhand, the formula initially in cell B1 of sheet 1, i.e., “=A1,” is aformula that refers exclusively to cells located within the region ofcells that are cut-and-paste (i.e., the region A1:B1). Accordingly, theset-cell command is updated to indicate the new location of the cell towhich it refers. That is, the formula is updated from “=A1” in sheet 1to “=A2” in sheet 2.

Next consider the formulas in cells A2:B2, which are not moved as partof the cut-and-paste operation. Applying the same rule to these cells,the set-cell command stored in cell A2 of sheet 1 is not updated. Thisis because the set-cell command, i.e., “SUM(A1:C1),” refers to at leastone cell outside of the cut-and-paste region (i.e., the cell C1, whichis outside the region A1:B1). Therefore, the entire set-cell command ofcell B2 of sheet 1 remains unchanged by the cut-and-paste operation, asshown by comparing this cell in sheet 1 900-1 and sheet 1 900-2. On theother hand, the formula of cell A2 of sheet 1 is updated as part of thecut-and-paste operation because this set-cell command refers exclusivelyto cells (actually, a single cell, cell B1) located within the region ofcells that are cut-and-paste (i.e., the region A1:B1). This update isseen by comparing the formula entered into cell A2 in sheet 1 900-1 andsheet 1 900-2.

FIG. 10 illustrates a workflow 1000 in which a cut-and-paste mutation isprocessed at a server in a collaborative spreadsheet environment inaccordance with an implementation. At 1010, a data edit mutation isreceived at a server from a client computer of a collaborator A and acut-and-paste mutation is received from a client computer of acollaborator B. Further the data edit operation has a higher prioritythan the cut-and-paste operation. In an implementation, the data editoperation corresponds to one of an insert row or column, delete row orcolumn, or a set-cell command applicable to one or more cells of thespreadsheet.

At 1020, the cut-and-paste mutation is transformed against the data editoperation to produce a modified cut-and-paste mutation. In particular,the operational transform is performed using some of techniquesdescribed above, depending of the type of data edit mutation (e.g.,whether an insert row or column, delete row or column, or a set-cellmutation) received from client computer A. At 1030, the values of thedestination (or “paste”) cells are set based on the modifiedcut-and-paste mutation. At 1040, values of the source (or “cut”) cellsare cleared. At 1050, any formulas referring to the source range ofcells or any subset of the source range of cells are adjusted accordingto the rule, described above, that the set-cell command is updated onlythe set-cell command exclusively references cells located within theregion of cells that is cut.

Generated Mutations for Structured Data Models

Conflicting mutations also arise in relation to user commands to editproperties of a cell (or cells) in a collaborative spreadsheetenvironment, and these conflicts may also be resolved using operationaltransforms. In particular, specified herein are both a cell propertymutation structure and a technique for applying operational transformsto cell property mutations.

Each cell in a spreadsheet is associated with a large number of cellproperties. For example, in an illustrative implementation, a portion ofa data structure storing properties for a single cell is expressed asfollows

message Format { optional Background background; optional Bordersborders; optional TextFormat cell_text_format; optional HorizontalAlignhorizontal_align; optional VerticalAlign vertical_align; optionalWrapStrategy wrap_strategy; optional NumberFormat number_format },where Background refers to properties of a cell background, Bordersrefers to properties of cell borders, TextFormat refers to theformatting of text displayed in the cell, VerticalAlign refers to avertical alignment of data in a cell, WrapStrategy refers to whether andhow text wraps within a cell, and NumberFormat refers to a formatting ofnumerical data, if present, within the cell. Further, some or all of thefields of the data structure shown above may include one or more levelsof nested subfields. For example, in an implementation, the bordersfield has subfields for a top border, bottom border, left border, andright border. Each of these four subfields, in turn, includes its ownsubfields for style, width, and color.

As disclosed herein, there are generally two types of mutations relatedto changes to cell properties. A “merge” mutation is a mutation in whicha user selects to edit a specific cell property or properties.Accordingly, to remain compact, a merge mutation includes datarepresentations for only those fields of the structured data model thatare to be edited and omits data represents for all other fields. Forexample, a change to a background color of a cell would be representedby a merge mutation of the form

cell_properties.format.background=< . . . >,

while a change to a background and foreground color of a cell would berepresented by a merge mutation of the form

cell_properties.format.{

-   -   background=< . . . >,    -   foreground=< . . . >}.

The second type of mutation is a “replace” mutation. The replacemutation is associated with cut-and-paste commands. Specifically, when auser pastes cells into a destination, the destination cells inherit allthe cell properties (among those specified in the structured data model)of the source cell. Thus, a “replace” mutation includes every field ofthe structured data model.

As mentioned above, cell property mutations entered by two collaboratorsto a common spreadsheet may conflict. Accordingly, process 1100 of FIG.11 illustrates an operational transform process by which cell propertymutations entered by two collaborators of a common spreadsheet areresolved in accordance with an implementation. The process 1100 startsat 1105. At 1110, a cell property mutation is received from each of aclient computer of collaborator A (this mutation will be referred to as“mutation A”) and a client computer of a collaborator B (this mutationwill be referred to as “mutation B”) that affects a common cell of aspreadsheet. At 1120, it is determined if both mutation A and mutation Bare replace mutations. If both of the mutations received at 1110originate from cut-and-paste operations, then both of the cell propertymutations are replacement mutations. In this case, process 1100 proceedsto 1130. At 1130, one of mutation A and mutation B is selected as awinning mutation, i.e., the mutation that is to be applied to theexclusion of the other mutation. The winning mutation may be selected byan arbitrary policy, but must be consistently applied at the server andat each client computer. For example, according to one implementation,the winning mutation is selected as the mutation between mutation A andmutation B that arrived at the server first. If, on the other hand, thecondition of 1120 is not satisfied, then the process 1100 continues to1140.

At 1140, it is determined if both mutation A and mutation B are mergemutations. If so, process 1100 proceeds to 1150. At 1150, the conflictbetween mutation A and mutation B is resolved by merging non-conflictingsubfields of mutation A and mutation B and selecting a winning subfieldof mutation A and mutation B for any conflicting subfields. Inparticular, a winning subfield among conflicting subfields of mutation Aand mutation B may be selected by an arbitrary policy, but must beconsistently applied at the server and at each client computer.

As an example of a case where the subfields of mutation A and mutation Bdo not conflict, suppose that mutation A is a change to a backgroundcolor of a cell, i.e., cell_properties.format.background=< . . . >, andmutation B is a change to a foreground color of the cell, i.e.,cell_properties.format.foreground=< . . . >. In this case, the subfieldsare merged to produce the mutation

-   -   cell_properties.format.{        -   background=< . . . >,        -   foreground=< . . . >}.            As an example of a case where mutation A and mutation B each            include a conflicting subfield, consider the case where            mutation A and B each specify (conflicting) edits of a            background color subfield of a cell, e.g., mutation A            specifies cell_properties.format.background=<color<type:RGB            rgb:0000CD>> and mutation B specifies            cell_properties.format.background=<color<type:RGB            rgb:ADD8E6>>. In this case, one of these subfields is            selected as the winning subfield. Again, the winning            subfield may be selected by an arbitrary policy, but must be            consistently applied.

If, on the other hand, the condition of 1140 is not satisfied, thismeans that one of mutation A and B is a merge mutation and the other isa replace mutation. In this case, the process 1100 proceeds to 1170. At1170, the replace mutation is selected as the winning mutation over themerge mutation.

It will be apparent to one of ordinary skill in the art, based on thedisclosure and teachings herein, that aspects of the disclosedtechniques, as described above, may be implemented in many differentforms of software, firmware, and hardware in the implementationsillustrated in the figures. The actual software code or specializedcontrol hardware used to implement aspects consistent with theprinciples of the disclosed techniques are not limiting. Thus, theoperation and behavior of the aspects of the disclosed techniques weredescribed without reference to the specific software code—it beingunderstood that one of ordinary skill in the art would be able to designsoftware and control hardware to implement the aspects based on thedescription herein.

1-10. (canceled)
 11. A method for updating a spreadsheet based on userinputs, the method comprising: receiving a first mutation of a firstpriority from a first source location specifying a function to beapplied to data of the spreadsheet, the first mutation comprising one ormore source-row-to-destination-row mappings; receiving a second mutationof a second priority from a second source location specifying a dataedit mutation to be applied to the spreadsheet, wherein the secondpriority is higher than the first priority; applying the second mutationto the spreadsheet; performing an operational transform on the firstmutation based on the second mutation to generate a modified firstmutation; and applying the modified first mutation to the spreadsheet.12. The method of claim 11, wherein: the first mutation corresponds to acut-and-paste operation, and the first mutation further comprises one ormore source column to destination column mappings.
 13. The method ofclaim 12, wherein applying the modified first mutation to thespreadsheet further comprises: setting values of destination cells ofthe spreadsheet in the cut-and-paste operation, clearing values ofsource cells of the spreadsheet in the cut-and-paste operation, andupdating one or more formulas in the spreadsheet that reference sourcecell locations in the spreadsheet.
 14. The method of claim 13, whereinupdating one or more formulas in the spreadsheet that reference sourcecell locations in the spreadsheet comprises: identifying a formulaassociated with a cell of the spreadsheet, and updating the formula onlyif it is determined that the formula references only cells that areincluded within the source cell locations in the spreadsheet.
 15. Themethod of claim 12, wherein the second mutation corresponds to a commandto delete a row within a region of source cells of the cut-and-pasteoperation in the spreadsheet, and performing the operational transformcomprises omitting contents of the deleted row from a region ofdestination cells of the cut-and-paste operation in the spreadsheet. 16.The method of claim 12, wherein the second mutation corresponds to acommand to insert a row within a region of destination cells of thecut-and-paste operation in the spreadsheet, and performing theoperational transform comprises preserving a location and content of theinserted row during the cut-and-paste operation.
 17. The method of claim11, wherein: the first mutation corresponds to a column sort operation,the one or more row mappings comprises one or moresource-row-to-destination-row-mappings, and the first mutation furthercomprises at least one value of cell data stored in each of the one ormore source rows.
 18. The method of claim 17, wherein the first sourceis a first client computer and the second source is a second clientcomputer, and wherein the first mutation and the second mutation arereceived at a server.
 19. The method of claim 17, wherein: the firstsource is a first client computer and the second source is a secondclient computer located remotely to the first client computer, and thefirst client computer performs the operational transform of the firstmutation.
 20. The method of claim 19, wherein the first client computerperforms the operational transform of the first mutation without readingfrom or writing to a chunk of the spreadsheet including a source row ofthe cut-and-paste operation.
 21. A computing system configured to updatea spreadsheet based on user inputs, the computing system comprising: anetwork interface configured to: receive a first mutation of a firstpriority from a first source location specifying a function to beapplied to data of the spreadsheet, the first mutation comprising one ormore source-row-to-destination-row-mappings; and receive a secondmutation of a second priority from a second source location specifying adata edit mutation to be applied to the spreadsheet, wherein the secondpriority is higher than the first priority; a processor configured to:apply the second mutation to the spreadsheet; perform an operationaltransform on the first mutation based on the second mutation to generatea modified first mutation; and apply the modified first mutation to thespreadsheet.
 22. The computing system of claim 21, wherein: the firstmutation corresponds to a cut-and-paste operation, and the firstmutation further comprises one or more source column to destinationcolumn mappings.
 23. The computing system of claim 22, wherein theprocessor is further configured to apply the modified first mutation tothe spreadsheet by: setting values of destination cells of thespreadsheet in the cut-and-paste operation, clearing values of sourcecells of the spreadsheet in the cut-and-paste operation, and updatingone or more formulas in the spreadsheet that reference source celllocations in the spreadsheet.
 24. The computing system of claim 23,wherein the processor is further configured to update one or moreformulas in the spreadsheet that reference source cell locations in thespreadsheet by: identifying a formula associated with a cell of thespreadsheet, and updating the formula only if it is determined that theformula references only cells that are included within the source celllocations in the spreadsheet.
 25. The computing system of claim 22,wherein the second mutation corresponds to a command to delete a rowwithin a region of source cells of the cut-and-paste operation in thespreadsheet, and performing the operational transform comprises omittingcontents of the deleted row from a region of destination cells of thecut-and-paste operation in the spreadsheet.
 26. The computing system ofclaim 22, wherein the second mutation corresponds to a command to inserta row within a region of destination cells of the cut-and-pasteoperation in the spreadsheet, and performing the operational transformcomprises preserving a location and content of the inserted row duringthe cut-and-paste operation.
 27. The computing system of claim 21,wherein: the first mutation corresponds to a column sort operation, theone or more row mappings comprises one or moresource-row-to-destination-row-mappings, and the first mutation furthercomprises at least one value of cell data stored in each of the one ormore source rows.
 28. The computing system of claim 27, wherein thefirst source is a first client computer and the second source is asecond client computer, and wherein the first mutation and the secondmutation are received at a server.
 29. The computing system of claim 27,wherein: the first source is a first client computer and the secondsource is a second client computer located remotely to the first clientcomputer, and the first client computer is configured to perform theoperational transform of the first mutation.
 30. The computing system ofclaim 29, wherein the first client computer is configured to perform theoperational transform of the first mutation without reading from orwriting to a chunk of the spreadsheet including a source row of thecut-and-paste operation. 31-44. (canceled)